const mysql = require('mysql2/promise');
const CONFIG = require('./config');

// 创建数据库连接池
const pool = mysql.createPool(CONFIG.DB);

// 数据库初始化函数
async function initializeDatabase() {
    try {
        const connection = await pool.getConnection();
        
        // 创建用户表
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                openid VARCHAR(255) UNIQUE NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                allergens JSON,
                allergens_set_at TIMESTAMP NULL,
                INDEX idx_openid (openid)
            )
        `);

        // 创建用户设置表
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS user_settings (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                age INT,
                gender ENUM('male', 'female'),
                dietary_preferences JSON,
                health_goals JSON,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                INDEX idx_user_id (user_id)
            )
        `);

        // 创建食谱表
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS recipes (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                time INT NOT NULL,
                category ENUM('中式', '西式', '饮品') NOT NULL,
                ingredients JSON NOT NULL,
                steps TEXT NOT NULL,
                nutrition TEXT NOT NULL,
                calories INT,
                protein INT,
                carbs INT,
                fat INT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_category (category),
                INDEX idx_time (time)
            )
        `);

        // 创建AI生成菜单记录表
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS ai_menus (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                menu_data JSON NOT NULL,
                time_constraint INT,
                meal_type VARCHAR(50),
                generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                INDEX idx_user_id (user_id),
                INDEX idx_generated_at (generated_at)
            )
        `);

        // 插入示例食谱数据
        await insertSampleRecipes(connection);

        console.log('数据库初始化完成');
        connection.release();
    } catch (error) {
        console.error('数据库初始化失败:', error);
        throw error;
    }
}

// 插入示例食谱数据
async function insertSampleRecipes(connection) {
    const sampleRecipes = [
        {
            name: "香蕉燕麦粥",
            time: 5,
            category: "中式",
            ingredients: JSON.stringify(["香蕉1根", "燕麦片50g", "牛奶200ml", "蜂蜜适量"]),
            steps: "1. 香蕉压成泥\n2. 加入燕麦片和牛奶\n3. 微波炉加热2分钟\n4. 加入蜂蜜调味",
            nutrition: "富含膳食纤维和钾，低GI值",
            calories: 280,
            protein: 8,
            carbs: 45,
            fat: 5
        },
        {
            name: "鸡蛋三明治",
            time: 10,
            category: "西式",
            ingredients: JSON.stringify(["全麦面包2片", "鸡蛋1个", "生菜2片", "番茄酱适量"]),
            steps: "1. 鸡蛋煮熟切片\n2. 面包烤至微黄\n3. 铺上生菜和鸡蛋\n4. 加入番茄酱对切",
            nutrition: "优质蛋白质来源，饱腹感强",
            calories: 320,
            protein: 15,
            carbs: 35,
            fat: 12
        },
        {
            name: "酸奶水果杯",
            time: 5,
            category: "饮品",
            ingredients: JSON.stringify(["希腊酸奶150g", "蓝莓50g", "草莓3颗", "坚果碎适量"]),
            steps: "1. 酸奶倒入杯中\n2. 加入切好的水果\n3. 撒上坚果碎\n4. 冷藏后食用更佳",
            nutrition: "富含益生菌和抗氧化物质",
            calories: 200,
            protein: 12,
            carbs: 25,
            fat: 8
        }
    ];

    for (const recipe of sampleRecipes) {
        try {
            await connection.execute(
                `INSERT IGNORE INTO recipes (name, time, category, ingredients, steps, nutrition, calories, protein, carbs, fat) 
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
                [recipe.name, recipe.time, recipe.category, recipe.ingredients, 
                 recipe.steps, recipe.nutrition, recipe.calories, recipe.protein, 
                 recipe.carbs, recipe.fat]
            );
        } catch (error) {
            // 忽略重复插入错误
            if (!error.message.includes('Duplicate entry')) {
                console.error('插入食谱失败:', error);
            }
        }
    }
}

// 数据库查询辅助函数
async function query(sql, params = []) {
    try {
        const [rows] = await pool.execute(sql, params);
        return rows;
    } catch (error) {
        console.error('数据库查询错误:', error);
        throw error;
    }
}

// 获取单个结果
async function queryOne(sql, params = []) {
    const rows = await query(sql, params);
    return rows[0] || null;
}

module.exports = {
    pool,
    initializeDatabase,
    query,
    queryOne
};